with tab_report_recycleoption_s1 as 
--主表，根据中心和网点去重，多次运行数据可能会不一样
(
    select  t.center_name       --规划转运中心
           ,t.center_code       --规划转运中心code
           ,t.network_name      --网点
           ,t.network_code      --网点code 
           ,t.entrpot_name      --集散点1
           ,t.entrpot_code      --集散点1code
           ,t.next_entrpot_name --集散点2
           ,t.next_entrpot_code --集散点2code
           ,t.first_shift_time  --1散时间
           ,nvl(t.first_center_out_line_day,0) as first_center_out_line_day  --1散派件跨天 
           ,t.second_shift_time   --2散时间
           ,nvl(t.second_center_out_line_day,0) as seconddeliverspanday --2散派件跨天  
           ,t.third_shift_time as third_shift_time --3散时间
           ,nvl(t.third_center_out_line_day,0) as thirddeliverspanday --3散派件跨天        
           ,row_number()over(partition by t.center_code,t.network_code 
                                 order by case when  t.entrpot_code is null then 3 
                                               when  t.entrpot_code is not null and  t.next_entrpot_code is null then 2  
                                               when  t.entrpot_code is not null and  t.next_entrpot_code is not null then 1  
                                           end desc ) as rn 
           ,first_arrival_time  --  1散规划到达时间
           ,second_arrival_time --  2散规划到达时间
           ,third_arrival_time  --  3散规划到达时间
     from  jms_dim.dim_tab_bulk_cargo_shift_config_base t
     where     to_date(t.create_time) = date_add('{{ execution_date | cst_ds }}', -2) 
)
--取一段码
, first_code as (
    select
      *
    from
      (
        select *,
          row_number() over(partition by code order by flg desc) as rn
        from
          (
               select
                 code                       --一段码code
                ,t.dest_center_code  center_code   --目的中心
                ,t.dest_center_name  center_name   --目的中心
                ,2 as flg
                from   jms_dim.dim_lmdm_sys_first_code t
                where t.is_enable = '1' and t.is_delete = '1'
                group by                  
                 code  
                ,dest_center_code     
                ,dest_center_name     
            union all
              select
                 express_center_short_code as code --一段码code
                ,express_center_code   center_code --目的中心
                ,t1.name               center_name --目的中心
               ,1 as flg
            from jms_dim.dim_yl_sys_package_different_base t
            left join  jms_dim.dim_network_whole_massage t1 on t.express_center_code=t1.code
            group by                  
                 express_center_short_code  
                ,express_center_code     
                ,t1.name

          ) t
      ) t
    where rn = 1
)
--取二段码
,dim_lmdm_sys_second_code as (
 
        select
             t3.code  as second_code
            ,t3.network_name   --网点
            ,t3.network_code   --网点
            ,t3.center_name    --中心
            ,t3.center_code    --中心
            ,t4.code as first_code
        from  jms_dim.dim_lmdm_sys_second_code t3
        left join first_code t4 on t3.center_code=t4.center_code
        group by 
             t3.code
            ,t3.network_name
            ,t3.network_code
            ,t3.center_name
            ,t3.center_code
            ,t4.code
)


--网点类型，1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点'
--最新操作扫描类型 1-快件揽收 3-发件扫描  5-中心到件 
,dwd_wide_yl_rt_ssmx_bill_detail_hf as (
    select 
       t.bill_code                          as waybill_no                     --运单号
      ,t.latest_scan_date                   as scan_time                      --扫描时间
      ,t.latest_next_station_code           as next_station_code              --下一站中心
      ,t3.name                              as next_station_name              --下一站中心
      ,t3.network_type                      as next_station_type              --下一站中心类型
      ,t.transfer_code                      as shipment_no                    --任务单
      ,t.latest_scan_code                   as scan_site_code                 --扫描网点
      ,t.latest_scan_name                   as scan_site_name                 --扫描网点
      ,t4.network_type                      as scan_network_type              --扫描网点类型    
      ,t2.center_code                       as dest_center_code               --目的中心
      ,t2.center_name                       as dest_center_name               --目的中心                                          
      ,t2.network_code                      as dest_network_code              --目的网点
      ,t2.network_name                      as dest_network_name              --目的网点
      ,t.latest_scan_type                   as latest_scan_type               --最新操作扫描类型
      ,to_date(t.latest_scan_date)          as diff_scan_time
    from  jms_dwd.dwd_wide_yl_rt_ssmx_bill_detail_hf t 
    
    left join dim_lmdm_sys_second_code t2 on split(t.terminal_dispatch_code, ',')[0]=t2.first_code
          and split(t.terminal_dispatch_code, ',')[1]=t2.second_code
          
    left join jms_dim.dim_network_whole_massage t3 on t.latest_next_station_code=t3.code
    
    left join jms_dim.dim_network_whole_massage t4 on t.latest_scan_code=t4.code

    where t.dt between date_add('{{ execution_date | cst_ds }}', -5) and '{{ execution_date | cst_ds }}' 
          and latest_scan_type in('1','3','4')
         
)
--最新操作扫描类型 1-快件揽收 3-发件扫描  5-中心到件 
--干支线数据量
,dwd_tab_barscan_send as (

    select 
        t.waybill_no                                                    --运单号
       ,t.scan_time                                                     --扫描时间
       ,t.next_station_code                                             --下一站中心
       ,t.next_station_name                                             --下一站中心
       ,t.next_station_type                                             --下一站中心类型
       ,t.shipment_no                                                   --任务单
       ,t.scan_site_code                                                --扫描网点
       ,t.scan_site_name                                                --扫描网点
       ,t.scan_network_type                                             --扫描网点类型
       ,t.dest_center_code                                              --目的中心
       ,t.dest_center_name                                              --目的中心
       ,t.dest_network_code                                             --目的网点
       ,t.dest_network_name                                             --目的网点
       ,t1.planned_arrival_time                                         --计划到件时间   
      ,case when t.scan_network_type = 4 then 1 
            when t.scan_network_type = 6 then 2 else 0 end  as  line_type  --1干线，2干线
    from  dwd_wide_yl_rt_ssmx_bill_detail_hf t 
    left join jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi t1 on t.shipment_no=t1.shipment_no and t.next_station_code=t1.network_code and t1.dt >= date_add('{{ execution_date | cst_ds }}', -30) and t1.dt <= date_add('{{ execution_date | cst_ds }}', 3)
    where t.diff_scan_time between date_add('{{ execution_date | cst_ds }}', -3) and '{{ execution_date | cst_ds }}'
          and t.latest_scan_type =3
          and t.next_station_code=t.dest_center_code  --发件下一站等于目的中心的

)
--中心发件数据
,dwd_tab_barscan_send_2 as (

    select
        t.waybill_no                                                    --运单号
       ,t.scan_time                                                     --扫描时间
       ,t.dest_center_code                                              --目的中心
       ,t.dest_center_name                                              --目的中心
       ,t.dest_network_code                                             --目的网点
       ,t.dest_network_name                                             --目的网点
    from  dwd_wide_yl_rt_ssmx_bill_detail_hf t
    where t.diff_scan_time between date_add('{{ execution_date | cst_ds }}', -3) and '{{ execution_date | cst_ds }}'
          and t.latest_scan_type =3
          and t.next_station_code<>4            --一下站不是中心
          and t.scan_site_code=dest_center_code --发件网点等于目的中心

)
--到件数据
--最新操作扫描类型 1-快件揽收 3-发件扫描  5-中心到件
,dwd_tab_barscan_centerarrival as (
    select
        t.waybill_no                     --运单号
       ,t.scan_time                      --扫描时间
       ,t.dest_center_code               --目的中心
       ,t.dest_center_name               --目的中心
       ,t.dest_network_code              --目的网点
       ,t.dest_network_name              --目的网点
    from  dwd_wide_yl_rt_ssmx_bill_detail_hf t
    left join jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi t1 on t.shipment_no=t1.shipment_no and t.next_station_code=t1.network_code
    where t.diff_scan_time between date_add('{{ execution_date | cst_ds }}', -3) and '{{ execution_date | cst_ds }}'
          and t.latest_scan_type =4
          and t.scan_site_code=t.dest_center_code  --扫描网点等于目的中心的

)
--近三天揽收数据
--最新操作扫描类型 1-快件揽收 3-发件扫描  5-中心到件
,dwd_tab_barscan_taking as (
    select
        t.waybill_no                     --运单号
       ,t.scan_time                      --扫描时间
       ,t.dest_center_code               --目的中心
       ,t.dest_center_name               --目的中心
       ,t.dest_network_code              --目的网点
       ,t.dest_network_name             --目的网点
       ,t.scan_site_code
       ,count(1) over(partition by scan_site_code,dest_network_code)  as total_num

       ,count(1) over(partition by dest_center_code,dest_network_code)  as dest_num

       ,count(1) over(partition by dest_center_code,dest_network_code)
         / count(1) over(partition by scan_site_code,dest_network_code)  as dest_rate

    from  dwd_wide_yl_rt_ssmx_bill_detail_hf t
    left join jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi t1 on t.shipment_no=t1.shipment_no and t.next_station_code=t1.network_code
    where t.diff_scan_time between date_add('{{ execution_date | cst_ds }}', -2) and '{{ execution_date | cst_ds }}'
          and t.latest_scan_type =1

)

,dwd_tab_barscan_taking_3 as (
 select
    t.dest_center_code
   ,t.dest_network_code
   ,max(t.total_num*t.dest_rate*t2.site2center_rate) as three_num
 from dwd_tab_barscan_taking t
 left join ( select t.*
             from  jms_dim.dim_network_delivery_center_base_dt t
             where t.dt = '{{ execution_date | date_add(-2) | cst_ds }}'
            ) t2 on t.scan_site_code=t2.first_network_network_code and t2.first_center_network_code=t.dest_center_code

 group by
   t.dest_center_code
  ,t.dest_network_code
)

insert overwrite table bidefault.dm_centersend_warning_detail_hi partition ( dt )
select
 t.center_name                  --规划转运中心
,t.center_code                  --规划转运中心code
,t.network_name                 --网点
,t.network_code                 --网点code
,t.entrpot_name                 --集散点1
,t.entrpot_code                 --集散点1code
,t.next_entrpot_name            --集散点2
,t.next_entrpot_code            --集散点2code
,t.first_shift_time             --1散时间
,t.first_center_out_line_day    --1散派件跨天
,t.second_shift_time            --2散时间
,t.seconddeliverspanday         --2散派件跨天
,t.third_shift_time             --3散时间
,t.thirddeliverspanday          --3散派件跨天
,t.first_arrival_time           --1散规划到达时间
,t.second_arrival_time          --2散规划到达时间
,t.third_arrival_time           --3散规划到达时间

,t1.waybill_no            as send_waybill_no      --干支线——发件运单号
,t1.scan_time             as send_scan_time       --干支线——发件时间
,t1.line_type             as send_line_type       --干支线——发件类型
,t1.planned_arrival_time  as send_planned_arrival_time --干支线——发件计划到车时间

,null                     as arrival_waybill_no   --中心滞留——到件单号
,null                     as arrival_scan_time    --中心滞留——到件时间
,t3.three_num             as three_num            --已揽收未发的
,1 as flg
,null                     as send2_waybill_no   --中心发件———单号
,null                     as send2_scan_time    --中心发件———扫描时间

,case when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),' ',t.first_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),' ',t.second_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),' ',t.third_shift_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),1),' ',t.first_shift_time,':00')
     end as planned_sned_time

,case when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),t.first_center_out_line_day),' ',t.first_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),t.seconddeliverspanday),' ',t.second_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),t.thirddeliverspanday),' ',t.third_arrival_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(t1.planned_arrival_time)+1*60*60),'yyyy-MM-dd'),1+t.first_center_out_line_day),' ',t.first_arrival_time,':00')
     end as planned_arrival_time

,'{{ execution_date | cst_hour }}' as dt
from tab_report_recycleoption_s1  t
left join dwd_tab_barscan_send t1          on t.center_code=t1.dest_center_code and t.network_code=t1.dest_network_code
left join dwd_tab_barscan_taking_3 t3      on t.center_code=t3.dest_center_code and t.network_code=t3.dest_network_code
where t.rn =1 and t1.waybill_no is not null


union all

select
 t.center_name                  --规划转运中心
,t.center_code                  --规划转运中心code
,t.network_name                 --网点
,t.network_code                 --网点code
,t.entrpot_name                 --集散点1
,t.entrpot_code                 --集散点1code
,t.next_entrpot_name            --集散点2
,t.next_entrpot_code            --集散点2code
,t.first_shift_time             --1散时间
,t.first_center_out_line_day    --1散派件跨天
,t.second_shift_time            --2散时间
,t.seconddeliverspanday         --2散派件跨天
,t.third_shift_time             --3散时间
,t.thirddeliverspanday          --3散派件跨天
,t.first_arrival_time           --1散规划到达时间
,t.second_arrival_time          --2散规划到达时间
,t.third_arrival_time           --3散规划到达时间

,null as send_waybill_no        --干支线——单号
,null as send_scan_time         --干支线——发件时间
,null as send_line_type         --干支线——发件类型
,null as send_planned_arrival_time   --干支线——发件计划到车时间

,null                     as arrival_waybill_no   --中心滞留——单号
,null                     as arrival_scan_time    --中心滞留——到件时间
,t3.three_num             as three_num            --已揽收未发的
,2 as flg
,t1.waybill_no            as send2_waybill_no   --中心发件——单号
,t1.scan_time             as send2_scan_time    --中心发件——发件时间

,case when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),' ',t.first_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),' ',t.second_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),' ',t.third_shift_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),1),' ',t.first_shift_time,':00')
     end as planned_sned_time

,case when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),t.first_center_out_line_day),' ',t.first_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),t.seconddeliverspanday),' ',t.second_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(t1.scan_time)),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),t.thirddeliverspanday),' ',t.third_arrival_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(t1.scan_time)),'yyyy-MM-dd'),1+t.first_center_out_line_day),' ',t.first_arrival_time,':00')
     end as planned_arrival_time
,'{{ execution_date | cst_hour }}' as dt
from tab_report_recycleoption_s1  t
left join dwd_tab_barscan_send_2 t1        on t.center_code=t1.dest_center_code and t.network_code=t1.dest_network_code
left join dwd_tab_barscan_taking_3 t3      on t.center_code=t3.dest_center_code and t.network_code=t3.dest_network_code
where t.rn =1 and t1.waybill_no is not null


union all

select
 t.center_name                  --规划转运中心
,t.center_code                  --规划转运中心code
,t.network_name                 --网点
,t.network_code                 --网点code
,t.entrpot_name                 --集散点1
,t.entrpot_code                 --集散点1code
,t.next_entrpot_name            --集散点2
,t.next_entrpot_code            --集散点2code
,t.first_shift_time             --1散时间
,t.first_center_out_line_day    --1散派件跨天
,t.second_shift_time            --2散时间
,t.seconddeliverspanday         --2散派件跨天
,t.third_shift_time             --3散时间
,t.thirddeliverspanday          --3散派件跨天
,t.first_arrival_time           --1散规划到达时间
,t.second_arrival_time          --2散规划到达时间
,t.third_arrival_time           --3散规划到达时间

,null as send_waybill_no        --干支线——单号
,null as send_scan_time         --干支线——发件时间
,null as send_line_type         --干支线——发件类型
,null as send_planned_arrival_time   --干支线——发件计划到车时间

,t2.waybill_no            as arrival_waybill_no   --中心滞留——单号
,t2.scan_time             as arrival_scan_time    --中心滞留——到件时间
,t3.three_num             as three_num            --已揽收未发的
,3 as flg
,null                     as send2_waybill_no   --中心发件———单号
,null                     as send2_scan_time    --中心发件———扫描时间

,case when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),' ',t.first_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),' ',t.second_shift_time,':00')

      when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),' ',t.third_shift_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),1),' ',t.first_shift_time,':00')
     end as planned_sned_time

,case when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.first_shift_time and t.first_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),t.first_center_out_line_day),' ',t.first_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.second_shift_time and t.second_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),t.seconddeliverspanday),' ',t.second_arrival_time,':00')

      when date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'HH:mm')<t.third_shift_time and t.third_shift_time is not null
           then concat(date_add(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),t.thirddeliverspanday),' ',t.third_arrival_time,':00')

      else concat(date_add(date_format(from_unixtime(unix_timestamp(if(to_date(t2.scan_time)='{{ execution_date | cst_ds }}',t2.scan_time,'{{ execution_date | cst_ds }} 00:00:00'))+1*60*60),'yyyy-MM-dd'),1+t.first_center_out_line_day),' ',t.first_arrival_time,':00')
     end as planned_arrival_time

,'{{ execution_date | cst_hour }}' as dt
from tab_report_recycleoption_s1  t
left join dwd_tab_barscan_centerarrival t2 on t.center_code=t2.dest_center_code and t.network_code=t2.dest_network_code
left join dwd_tab_barscan_taking_3 t3      on t.center_code=t3.dest_center_code and t.network_code=t3.dest_network_code
where t.rn =1 and t2.waybill_no is not null
 distribute by pmod(hash(rand()), 120)

;





with dm_centersend_warning_sum_hi_1 as (

select
 center_name
,center_code
,network_name
,network_code
,entrpot_name
,entrpot_code
,next_entrpot_name
,next_entrpot_code
,first_shift_time
,first_center_out_line_day
,second_shift_time
,seconddeliverspanday
,third_shift_time
,thirddeliverspanday
,first_arrival_time
,second_arrival_time
,third_arrival_time
,send_waybill_no
,send_scan_time
,send_line_type
,send_planned_arrival_time
,arrival_waybill_no
,arrival_scan_time
,three_num
,flg
,send2_waybill_no
,send2_scan_time
,planned_sned_time
,planned_arrival_time

,date_add(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'yyyy-MM-dd') ,if(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'HH:mm')>='16:00',0,-1)) as query_date

from bidefault.dm_centersend_warning_detail_hi t
where t.dt='{{ execution_date | cst_hour }}'
and date_add(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'yyyy-MM-dd') ,if(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'HH:mm')>='16:00',0,-1) )>='{{ execution_date | cst_ds }}'
and date_add(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'yyyy-MM-dd') ,if(date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'HH:mm')>='16:00',0,-1) )<=date_add('{{ execution_date | cst_ds }}' ,1)

)

, tab_report_recycleoption_s1 as
--主表，根据中心和网点去重，多次运行数据可能会不一样
(
select t.*,scan_site_no+1   as scan_site_no
           ,node from (
    select  t.center_name       --规划转运中心
           ,t.center_code       --规划转运中心code
           ,t.network_name      --网点
           ,t.network_code      --网点code
           ,t.entrpot_name      --集散点1
           ,t.entrpot_code      --集散点1code
           ,t.next_entrpot_name --集散点2
           ,t.next_entrpot_code --集散点2code
           ,t.first_shift_time
           ,t.second_shift_time
           ,t.third_shift_time
           ,agent_code                                --代理区code
           ,agent_name                                --代理区
           ,row_number()over(partition by t.center_code,t.network_code
                                 order by case when  t.entrpot_code is null then 3
                                               when  t.entrpot_code is not null and  t.next_entrpot_code is null then 2
                                               when  t.entrpot_code is not null and  t.next_entrpot_code is not null then 1
                                           end desc ) as rn
     from  jms_dim.dim_tab_bulk_cargo_shift_config_base t
     where to_date(t.create_time) = date_add('{{ execution_date | cst_ds }}', -2)
    ) t
     lateral view posexplode(array(first_shift_time,second_shift_time,third_shift_time)) tmp as scan_site_no,node

     where t.rn =1

)




insert overwrite table jms_dm.dm_centersend_warning_sum_hi partition ( dt )
select
 t1.center_code                               --规划转运中心code
,t1.center_name                               --规划转运中心
,t1.entrpot_code                              --集散点1code
,t1.entrpot_name                              --集散点1
,t1.next_entrpot_code                         --集散点2code
,t1.next_entrpot_name                         --集散点2
,t1.network_code                              --网点code
,t1.network_name                              --网点
,t1.agent_code                                --代理区code
,t1.agent_name                                --代理区
,t1.scan_site_no  as  shift_classes           --散货班次
,t.planned_sned_time                          --规划发车时间
,t.planned_arrival_time                       --规划到车时间
,count(case when t.flg=1 and t.send_line_type=1 then t.send_waybill_no else null end ) as center_send_num  --干线在途
,count(case when t.flg=1 and t.send_line_type=2 then t.send_waybill_no else null end ) as site_send_num    --支线在途
,count(case when t.flg=3  then t.arrival_waybill_no else null end ) as center_stop_num                     --中心滞留
,max(three_num) as first_siet_send_num                                                                     --网点已揽未发
,count(case when t.flg=2 then t.send2_waybill_no else null end ) as dest_center_send_num                        --中心发件数据
,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')  as etl_time
,t.query_date  --query_date   传参数查询日期天
,'{{ execution_date | cst_ds }}' as date_time    --传参数查询日期天
,'{{ execution_date | cst_hour }}' as dt        --分区小时
from tab_report_recycleoption_s1  t1
left join dm_centersend_warning_sum_hi_1 t  on t1.center_code=t.center_code
     and t1.network_code=t.network_code
     and t1.node=date_format(from_unixtime(unix_timestamp(t.planned_sned_time)),'HH:mm')
group by
 t1.center_name                  --规划转运中心
,t1.center_code                  --规划转运中心code
,t1.network_name                 --网点
,t1.network_code                 --网点code
,t1.entrpot_name                 --集散点1
,t1.entrpot_code                 --集散点1code
,t1.next_entrpot_name            --集散点2
,t1.next_entrpot_code            --集散点2code
,t1.agent_code                                --代理区code
,t1.agent_name                                --代理区
,t1.scan_site_no
,t.planned_sned_time
,t.planned_arrival_time
,t.query_date
 distribute by pmod(hash(rand()), 2)
